ajDBDeleteRecord function

Available since AlchemyJ v4.1

Description

The ajDBDeleteRecord function deletes records in a database table satisfying the criteria specified in filter_condition. Please take note that to run this function from Excel, you would need to set up the Data Source Connection in ##ExternalResources.

Syntax

ajDBDeleteRecord(table_name, [column_headers], [data], [filter_type], [filter_condition], [table_schema], [data_source_id], [run_condition], [run_by_function_point_only])

Argument Name	Argument Type	Description
table_name (required)	String	The name of the table where the records should be deleted from.
column_headers (Optional)	Range / Array	The columns to be matched. It can be a range of single row or column that each cell for a column. The cell value can be the actual column name or a data name which is defined in DB Schema. All key columns need to be specified. CLOB and BLOB columns are not supported.
data (Optional)	Range / Array	The range for the key field value(s) of the record to be deleted. The orientation can either be landscape or portrait. The orientation should be the same as column_headers.
filter_type (optional)	Double	The type of filtering. 3 types are supported 0, 1 and 2. You would need to either provide filter_type+filter_condition or the DB Schema to call this function. Otherwise, an error will be raised.
filter_condition (optional)	Range / Array	The range that defines the filter condition. filter_type = 0. The range defines the WHERE clause of a SQL statement. The range will be concatenated into a single string. For example, Name = 'peter' and class = 'B'. filter_type = 1. The range defines the filter condition in the Kendo grid style. Click Insert Snippet\Filter Condition (Filter Type 1) to add the preset required format. filter_type =2. The range defines a filter condition similar to the format used in MS Query. Click Insert Snippet\Filter Condition (Filter Type 2) to add the preset required format. Refer to the Filter Condition snippet to see how you can use this snippet to define the required fields for filter type 1, 2.
table_schema (optional)	Range / Array	The range that defines the DB Schema. You need to either provide the filter_type+filter_condition or the DB Schema to call this function. When the DB Schema is provided, the key column(s) must be included in the Column_headers and Data. Otherwise, an error will be raised.
data_source_id (optional)	String	The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary".
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: Number of affected records.

2) Return Type: Single Value / Multiple values (array formula)

Example

Before using the function, you need to set up the Data Source Connection in ##ExternalResources worksheet.

Before running this function point, you need to configure the Data Source ID in ##RestEndpointGroup worksheet or ##JavaApiClass.

We will use the following table in our examples. The table name is tb_customer. It has 5 columns and 5 rows.

Example 1 - Only contain column and data

This example deletes records from tb_customer where GENDER is M and CUSTOMER_LVL is 2.

Check the result from database.

Example 2 - Filter Type is 0

This example deletes records from tb_customer where CUSTOMER_LVL is 1.

Check the result from database.

Example 3 - Filter Type is 1

This example deletes records where GENDER = 'M' and (DOB ='1977-01-02' or DOB= '1988-04-06') using filter_type 1.

The filter condition table was created via AlchemyJ ribbon Insert Snippet and select Filter Condition (Filter Type 1) , make sure there are 5 empty rows, otherwise inserting Filter Condition will fail.

The values of parameters are as below, More details about Filter Condition, refer to Filter Condition.

Check the result from database.

Example 4 - Filter Type is 2

This example deletes records **where (GENDER = 'M' and DOB = '1977-01-02') or (DOB = '1988-04-06') **using filter_type 2. Refer to Example3 to add filter condition table.

The values of parameters are as below. More details about Filter Condition, refer to Filter Condition .

Check the result from database.

Add DB Schema, you can use the Data Name in DB Schema to replace the column names of the database table.

Click Add Component to and select DB Schema to add the DB Schema.

Configuration of Data Name column is as below:

Then you can use the Data Name in DB Schema to replace the table column names in filter_condition, remember that table_schema must reference to the DB Schema.

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
DB connection error.
Table name is empty.
Filter type is invalid, it must be 0, 1, or 2.
Data name in filter condition does not exist in the provided table schema.
Total number of filter condition columns is not even number.
Data name in filter condition is empty.
Operator in filter condition is empty.
Invalid operator value in filter condition.
And/Or in filter condition is invalid.
Content in filter condition range does not meet the expected format of specified filter type.
Either filter condition or table schema must be provided.
Table name does not exist in provided DB Schema.
The table schema columns are invalid.
The table schema range does not include a header or a row of data.
The provided column header cannot be found in the specified table schema range.
Column header is not a single row range or a single column range.
Number of columns in column_headers does not match with the number of data columns.
Some data columns are empty.
Data is provided without table_schema.
Table schema does not have key order column.
Value for Key column is empty.
The filter condition does not include key column.